
*-------------------------------------------------------------------------------
*
* Paper: A Lockdown Index to Assess the Distributional Impact of the Coronavirus
* Authors: Marius Faber, Andrea Ghisletta, Kurt Schmidheiny
* Date:    June 2020
*
*
* REPLICATION FILE: 
* A_01_data.do
*
* DESCRIPTION:
* 0 Settings: 		   			Defines system settings, downloads packages, 
* 								sets working directory, starts logging.
* 1 Occupational requirements: 	Imports data on occupational physical proximity 
*								requirements and teleworkability, and translates 
* 								these from SOC to ISCO08 classification.
* 2 Swiss labor force data:  	Imports SLFS 2018 data, selects population of 
* 								interest, weights variables, corrects for 
* 								essential and public sectors, creates variables 
*								of interest, and saves data in privacy-conform 
*								format.
* ------------------------------------------------------------------------------


* ------------------------------------------------------------------------------
* 0 Settings 
* ------------------------------------------------------------------------------

* 0.1 System settings
clear 				all
set more 			off, perm
pause 				on
set autotabgraphs 	on, perm 

* 0.2 Set working directories
cd "yourpath" 						// replace with path containing this replication folder
global SLFSpath	"yourSLFSpath" 		// replace with path containing SLFS data and SLFS municipal numbers

* 0.3 Download packages
/*
ssc install _gwtmean, replace
*/

* 0.4 Start logging
cap log close
cap log using "D_01_data.log", append

* ------------------------------------------------------------------------------
* 1 Occupational requirements 
* ------------------------------------------------------------------------------

* 1.1 Physical proximity requirements
* Import raw data from O*NET
import excel "B_01_input_proximity.xlsx", sheet("stata") firstrow

* Keep only average score
keep if scale_id=="CX"

* Generate index
gen proximity=.
replace proximity=0   if data_value>=0 & data_value<3
replace proximity=0.5 if data_value>=3 & data_value<4
replace proximity=1   if data_value>=4
keep onetsoccode title proximity
save "B_02_temp_proximity_6d.dta", replace

* 1.2 Teleworkability
* Import raw data from Dingel and Neiman (2020) from https://github.com/jdingel/DingelNeiman-workathome/tree/master/occ_onet_scores/output
import delimited using "B_01_input_homeoffice.csv",  clear

* Merge with physical proximity requirements
merge 1:1 onetsoccode using "B_02_temp_proximity_6d.dta"
drop _merge title

* Index is at 6-digit SOC level, conversion table to ISCO on 5-digit level. Convert index to higher level, taking means with equal weights in case of distinct values
gen onetsoccode_6d    =substr(onetsoccode,1,7)
replace onetsoccode_6d=subinstr(onetsoccode_6d,"-","",1)
gen onetsoccode_5d6d  =substr(onetsoccode_6d,1,5)

bys onetsoccode_6d: egen telew_many=mean(teleworkable)
bys onetsoccode_6d: egen proximity_many=mean(proximity)

drop teleworkable proximity
rename telew_many teleworkable
rename proximity_many proximity

destring onetsoccode_6d, replace
destring onetsoccode_5d6d, replace

contract proximity teleworkable onetsoccode_6d onetsoccode_5d6d
drop _freq
save "B_02_temp_requirements_soc.dta", replace 

* 1.3 Convert from SOC to ISCO classification
use "B_01_input_soc10_isco08.dta",  clear
rename soc10 onetsoccode_6d
merge m:1 onetsoccode_6d using "B_02_temp_requirements_soc.dta"
browse if _merge!=3 // 93 master not matched: information in O*Net missing. 2 SOC professions (191020, 292055) not available in ISCO-08 conversion table.
drop if _merge!=3
drop _merge
* create unique indexes for ISCO-08 codes (sometimes more SOC occupations refer to the same ISCO-08 occupation), weighting equally each SOC corresponding occupation.
drop if teleworkable==.
duplicates report isco08
bysort isco08: egen teleworkable_isco=mean(teleworkable)
bysort isco08: egen proximity_isco=mean(proximity)

contract isco08 teleworkable_isco proximity_isco
drop _freq
save "B_02_temp_requirements_isco.dta",  replace

* 1.4 Merge with ISCO titles for 4-digit level
* Prepare ISCO08 data
import excel "B_01_input_isco08_titles", firstrow sheet("isco08") clear
rename isco08 isco08string
destring isco08string, gen(isco08)
drop if isco08==.
sort isco08
duplicates drop isco08, force
replace isco08string="0110" if isco08string=="110"
replace isco08string="0210" if isco08string=="210"
replace isco08string="0310" if isco08string=="310"
gen isco08_3d=substr(isco08string,1,3)
gen isco08_2d=substr(isco08string,1,2)
gen isco08_1d=substr(isco08string,1,1)
destring isco08_*, replace

merge 1:1 isco08string using "B_01_input_isco08digits.dta"
drop if _merge==2
replace isco_digits=2 if isco08==100 |isco08==200|isco08==300
drop _merge
save "B_02_temp_isco08_titles.dta", replace

* Merge with job requirements
merge 1:1 isco08 using "B_02_temp_requirements_isco.dta"
bys isco_digits: tab _merge
drop if _merge!=3 & isco_digits==4
drop _merge

* 1.5 Compute mean values on higher ISCO levels because SLFS data are sometimes coded only at higher digit level
* 3-digit
preserve
keep if isco_digits==3 | isco_digits==4
bys isco08_3d: egen temp_tel_3d=mean(teleworkable_isco)
bys isco08_3d: egen temp_prox_3d=mean(proximity_isco)

keep isco08 temp_tel_3d temp_prox_3d
tempfile junk
save `junk', replace
restore
merge 1:1 isco08 using `junk', nogen

replace teleworkable_isco = temp_tel_3d  if missing(teleworkable_isco) & !missing(temp_tel_3d)
replace proximity_isco    = temp_prox_3d if missing(proximity_isco)    & !missing(temp_prox_3d)

drop temp_*

* 2-digit
preserve
keep if isco_digits==2 | isco_digits==3
bys isco08_2d: egen temp_tel_2d  = mean(teleworkable_isco) 
bys isco08_2d: egen temp_prox_2d = mean(proximity_isco)

keep isco08 temp_tel_2d temp_prox_2d
tempfile junk
save `junk', replace
restore
merge 1:1 isco08 using `junk', nogen

replace teleworkable_isco = temp_tel_2d  if missing(teleworkable_isco) & !missing(temp_tel_2d)
replace proximity_isco    = temp_prox_2d if missing(proximity_isco)    & !missing(temp_prox_2d)

drop temp_*

* 1-digit
preserve
keep if isco_digits==1 | isco_digits==2
bys isco08_1d: egen temp_tel_1d  = mean(teleworkable_isco)
bys isco08_1d: egen temp_prox_1d = mean(proximity_isco)

keep isco08 temp_tel_1d temp_prox_1d
tempfile junk
save `junk', replace
restore
merge 1:1 isco08 using `junk', nogen

replace teleworkable_isco = temp_tel_1d  if missing(teleworkable_isco) & !missing(temp_tel_1d)
replace proximity_isco    = temp_prox_1d if missing(proximity_isco)    & !missing(temp_prox_1d)

drop temp_*

save "B_02_temp_requirements.dta",  replace
erase "B_02_temp_requirements_soc.dta"
erase "B_02_temp_requirements_isco.dta"

* ------------------------------------------------------------------------------
* 2 Swiss labor force data 
* ------------------------------------------------------------------------------

* 2.1 Prepare municipality code (this step is not needed if yearly SLFS municipality numbers are available)
clear all

* Import quarterly data 
*convert "SAKE2018Q...-INTERN-B015 B019 EM07 LM07.csv" into .dta format

use "$SLFSpath\SAKE2018Q1.dta"
append using "$SLFSpath\SAKE2018Q2.dta", gen(q2)
append using "$SLFSpath\SAKE2018Q3.dta", gen(q3)
append using "$SLFSpath\SAKE2018Q4.dta", gen(q4)

* Generate quarter variable
gen quarter=.
replace quarter=1 if q2==0
replace quarter=2 if q2==1
replace quarter=3 if q3==1
replace quarter=4 if q4==1

*Rename variables
rename b015 household_nr
rename em07 mun_company
rename lm07 mun_company_appr

* Drop missing municipality information
drop if mun_company<0 & mun_company_appr<0
 
* Keep only most recent quarter if observation is available in multiple quarters
* Q1
duplicates tag household_nr, gen(dupli)
drop if quarter==1 & dupli==1
* Q2
drop dupli
duplicates tag household_nr, gen(dupli)
drop if quarter==2 & dupli==1
* Q3
drop dupli
duplicates tag household_nr, gen(dupli)
drop if quarter==3 & dupli==1
* Check
drop dupli
duplicates tag household_nr, gen(dupli)
browse if dupli==1

* Municipality number
gen firm_municipality=mun_company
replace firm_municipality=mun_company_appr if mun_company<0 & mun_company_appr>0 & !missing(mun_company_appr)

* Substitute old, incorrect municipal numbers
replace firm_municipality=296  if firm_municipality==174 	// Illnau Effretikon
replace firm_municipality=294  if firm_municipality==217 	// Elgg
replace firm_municipality=2054 if firm_municipality==2034 	// Murist now Estavayer
replace firm_municipality=5002 if firm_municipality==5005 	// Giubiasco now Bellinzona
replace firm_municipality=5002 if firm_municipality==5008 	// Gudo now Bellinzona
replace firm_municipality=5002 if firm_municipality==5019 	// Sementina now Bellinzona

* Import municipalities' geo information
merge m:1 firm_municipality using "B_01_input_crosswalk_mun_lmr_llmr.dta.dta"
drop if _merge==2
drop _merge

save "B_02_temp_geo_number.dta", replace

* 2.2 Import SLFS 2018
* Import SAKE and rename
import delimited using "$SLFSpath/SAKE2018.csv", clear
rename bfu5i isco08
label var isco08 "ISCO-08 job classification"

* Import Gemeindenummer
rename b015 household_nr
merge 1:1 household_nr using "B_02_temp_geo_number.dta"
tab  em06 if _merge==1 			// merge==1 are individuals with no firm locality
drop if _merge==2 				// municipalities not contained in sample
drop _merge

* 2.3 Merge with job requirements
merge m:1 isco08 using "B_02_temp_requirements.dta"
tab isco08 if _merge==2, m 		// jobs not contained in sample: few and coherent with Swiss occupations: for example "Traditional chiefs and heads of villages"
tab isco08 if _merge==1, m 		// jobs not matched: most from missing info about occupation in SLFS or because individual unemployed; occupations with isco08 number 110, 210, 310, 1111, 2659, 3413, 7133, 8159, 9613 have not an index because the corresponding SOC occupations were not available in Onet
drop if _merge==2
*drop if _merge==1
drop _merge

* 2.4 Create aggregate NOGA2008 codes that are used in other data sources (e.g., SECO unemployment data)
* NOGA2008 4-digits
rename em03 noga2008

destring noga2008, replace
gen str6 noga2008_str=string(noga2008,"%06.0f")
gen noga2008_4d=substr(noga2008_str, 1,4)
destring noga2008_4d, replace

* NOGA2008 2-digits
gen noga2008_2d=substr(noga2008_str, 1,2)
destring noga2008_2d, replace

* NOGA2008_seco (as used in unemployment data)
gen noga2008_seco=""
replace noga2008_seco="01-03" if noga2008_2d>=01 & noga2008_2d<=03
replace noga2008_seco="05-09" if noga2008_2d>=05 & noga2008_2d<=09
replace noga2008_seco="10-12" if noga2008_2d>=10 & noga2008_2d<=12
replace noga2008_seco="13-14" if noga2008_2d>=13 & noga2008_2d<=14
replace noga2008_seco="15"    if noga2008_2d==15
replace noga2008_seco="16"    if noga2008_2d==16
replace noga2008_seco="17-18" if noga2008_2d>=17 & noga2008_2d<=18
replace noga2008_seco="19-21" if noga2008_2d>=19 & noga2008_2d<=21
replace noga2008_seco="22"    if noga2008_2d==22
replace noga2008_seco="23"    if noga2008_2d==23
replace noga2008_seco="24-25" if noga2008_2d>=24 & noga2008_2d<=25
replace noga2008_seco="26-27" if noga2008_2d>=26 & noga2008_2d<=27
replace noga2008_seco="28"    if noga2008_2d==28
replace noga2008_seco="29-30" if noga2008_2d>=29 & noga2008_2d<=30
replace noga2008_seco="31-33" if noga2008_2d>=31 & noga2008_2d<=33
replace noga2008_seco="35"    if noga2008_2d==35
replace noga2008_seco="36-39" if noga2008_2d>=36 & noga2008_2d<=39
replace noga2008_seco="41-43" if noga2008_2d>=41 & noga2008_2d<=43
replace noga2008_seco="45"    if noga2008_2d==45
replace noga2008_seco="46"    if noga2008_2d==46
replace noga2008_seco="47"    if noga2008_2d==47
replace noga2008_seco="49-53" if noga2008_2d>=49 & noga2008_2d<=53
replace noga2008_seco="55-56" if noga2008_2d>=55 & noga2008_2d<=56
replace noga2008_seco="58-63" if noga2008_2d>=58 & noga2008_2d<=63
replace noga2008_seco="64-66" if noga2008_2d>=64 & noga2008_2d<=66
replace noga2008_seco="68"    if noga2008_2d==68
replace noga2008_seco="69-75" if noga2008_2d>=69 & noga2008_2d<=75
replace noga2008_seco="77-82" if noga2008_2d>=77 & noga2008_2d<=82
replace noga2008_seco="84"    if noga2008_2d==84
replace noga2008_seco="85"    if noga2008_2d==85
replace noga2008_seco="86-88" if noga2008_2d>=86 & noga2008_2d<=88
replace noga2008_seco="90-93" if noga2008_2d>=90 & noga2008_2d<=93
replace noga2008_seco="94-96" if noga2008_2d>=94 & noga2008_2d<=96
replace noga2008_seco="97-98" if noga2008_2d>=97 & noga2008_2d<=98

* NOGA2008_kza (as used in Kurzarbeit data)
gen noga2008_kza=noga2008_seco
replace noga2008_kza="45-47" if noga2008_seco=="45" | noga2008_seco=="46" | noga2008_seco=="47"

* NOGA2008_gg (as used in Grenzgänger data)
gen noga2008_gg=""
replace noga2008_gg="01-03"    if noga2008_2d>=01 & noga2008_2d<=03
replace noga2008_gg="05-09"    if noga2008_2d>=05 & noga2008_2d<=09
replace noga2008_gg="10-12"    if noga2008_2d>=10 & noga2008_2d<=12
replace noga2008_gg="13-15"    if noga2008_2d>=13 & noga2008_2d<=15
replace noga2008_gg="16-18"    if noga2008_2d>=16 & noga2008_2d<=18
replace noga2008_gg="19-20"    if noga2008_2d>=19 & noga2008_2d<=20
replace noga2008_gg="21"       if noga2008_2d==21
replace noga2008_gg="22-23"    if noga2008_2d>=22 & noga2008_2d<=23
replace noga2008_gg="24-25"    if noga2008_2d>=24 & noga2008_2d<=25
replace noga2008_gg="26"       if noga2008_2d==26
replace noga2008_gg="27"       if noga2008_2d==27
replace noga2008_gg="28"       if noga2008_2d==28
replace noga2008_gg="29-30"    if noga2008_2d>=29 & noga2008_2d<=30
replace noga2008_gg="31-33"    if noga2008_2d>=31 & noga2008_2d<=33
replace noga2008_gg="35"       if noga2008_2d==35
replace noga2008_gg="36-39"    if noga2008_2d>=36 & noga2008_2d<=39
replace noga2008_gg="41-42"    if noga2008_2d>=41 & noga2008_2d<=42
replace noga2008_gg="43"       if noga2008_2d==43
replace noga2008_gg="45"       if noga2008_2d==45
replace noga2008_gg="46"       if noga2008_2d==46
replace noga2008_gg="47"       if noga2008_2d==47
replace noga2008_gg="49"       if noga2008_2d==49
replace noga2008_gg="50-51"    if noga2008_2d>=50 & noga2008_2d<=51
replace noga2008_gg="52"       if noga2008_2d==52
replace noga2008_gg="53"       if noga2008_2d==53
replace noga2008_gg="55"       if noga2008_2d==55
replace noga2008_gg="56"       if noga2008_2d==56
replace noga2008_gg="58-60"    if noga2008_2d>=58 & noga2008_2d<=60
replace noga2008_gg="61"       if noga2008_2d==61
replace noga2008_gg="62-63"    if noga2008_2d>=62 & noga2008_2d<=63
replace noga2008_gg="64"       if noga2008_2d==64
replace noga2008_gg="65"       if noga2008_2d==65
replace noga2008_gg="66"       if noga2008_2d==66
replace noga2008_gg="68"       if noga2008_2d==68
replace noga2008_gg="69"       if noga2008_2d==69
replace noga2008_gg="70"       if noga2008_2d==70
replace noga2008_gg="71"       if noga2008_2d==71
replace noga2008_gg="72"       if noga2008_2d==72
replace noga2008_gg="73-75"    if noga2008_2d>=73 & noga2008_2d<=75
replace noga2008_gg="77,79-82" if noga2008_2d==77 | (noga2008_2d>=79 & noga2008_2d<=82)
replace noga2008_gg="78"       if noga2008_2d==78
replace noga2008_gg="84"       if noga2008_2d==84
replace noga2008_gg="85"       if noga2008_2d==85
replace noga2008_gg="86"       if noga2008_2d==86
replace noga2008_gg="87"       if noga2008_2d==87
replace noga2008_gg="88"       if noga2008_2d==88
replace noga2008_gg="90-93"    if noga2008_2d>=90 & noga2008_2d<=93
replace noga2008_gg="94-96"    if noga2008_2d>=94 & noga2008_2d<=96
replace noga2008_gg="97-98"    if noga2008_2d>=97 & noga2008_2d<=98
replace noga2008_gg="99"       if noga2008_2d==99

* 2.5 Label variables
* ISCO-08
replace isco_title="Does not match(?)" if isco08==-9
replace isco_title="No answer from respondent" if isco08==-8
replace isco_title="Respondent does not know own job classification" if isco08==-7
replace isco_title="Job classification not codable" if isco08==-5
replace isco_title="Job classification not asked (eg. unemployed)" if isco08==-1

* Full time equivalent
rename tek4 fulltime
label var fulltime " Full-time equivalent" 

* NOGA2008 1-digits
rename bmu3 NOGA_sector
label var NOGA_sector "Labor market sector of Firm, NOGA2008"
label define NOGA_lbl -9 "Does not match" -8 "No answer from respondent" -7 "Respondent does not know own job classification" -1 "Job classification not asked (eg. unemployed)" 1"Agriculture, hunting and forestry" 2 "Mining and quarrying" 3 "Manifacturing" 4 "Electricity, gas, steam and air conditioning supply" 5 "Water supply; Sewerage, waste management and remediation activities" 6 "Construction" 7 "Wholesale and retail trade; Repair of motor vehicles and motorcycles" 8 "Transportation and storage" 9 "Accomodation and food service activities" 10 "Information and communication" 11 "Financial and insurance activities" 12 "Real estate activities" 13 "Professional, scientific and technical activities" 14 "Administrative and support service activities" 15"Public administration and defence; Compulsory social security" 16 "Education" 17 "Human health and social work activities" 18 "Arts, entertainment and recreation" 19 "Other service activities" 20 "Activities of households as employers; Undifferentiated goods- and services-producing activities of households for own use" 21 "Activities of extraterritorial organisations and bodies"
label value NOGA_sector NOGA_lbl

* Arbeitsmarktgrossregionen 2018
rename Arbeitsmarktgrossregionen2018 llmr2018
label var llmr2018 "Great Labor market region 2018"
destring llmr2018, replace
label define llmr2018_lbl 1 "Genf" 2 "Lausanne" 3 "Neuchâtel" 4 "Fribourg" 5 "Biel–Jura" 6 "Bern" 7 "Westalpen" 8 "Basel" 9 "Berner Oberland" 10 "Aareland" 11 "Zentralschweiz" 12 "Zürich" 13 "Sopraceneri" 14 "Sottoceneri" 15 "     Bodenseeregion" 16 "Ostalpen"
label value llmr2018 llmr2018_lbl
decode llmr2018, gen(llmr2018_title)

* Arbeitsmarktregionen 2018
destring Arbeitsmarktregionen2018, gen(lmr2018)
recode lmr2018 (01011=1) (01012=2)(01013=3)(01014=4)(01015=5)(02010=6)(02021=7)(02022=8)(02023=9)(02024=10)(02030=11)(02040=12)(02050=13)(02060=14)(03010=15)(03020=16)(03030=17)(03040=18)(04010=19)(04020=20)(04030=21)(05010=22)(05020=23)(05030=24)(05040=25)(05050=26)(05060=27)(06011=28)(06012=29)(06013=30)(06014=31)(06015=32)(06020=33)(06030=34)(07010=35)(07020=36)(07030=37)(07040=38)(07050=39)(07060=40)(07070=41)(07080=42)(07090=43)(08011=44)(08012=45)(08013=46)(08014=47)(08015=48)(09010=49)(09020=50)(09030=51)(09040=52)(09050=53)(09060=54)(10010=55)(10020=56)(11010=57)(11020=58)(11030=59)(11040=60)(11050=61)(11060=62)(11070=63)(11080=64)(12010=65)(12020=66)(12031=67)(12032=68)(12033=69)(12034=70)(12035=71)(12036=72)(12037=73)(12038=74)(12040=75)(12050=76)(13010=77)(13020=78)(13030=79)(13040=80)(14010=81)(14020=82)(15010=83)(15020=84)(15030=85)(15040=86)(15050=87)(15060=88)(15070=89)(15080=90)(16010=91)(16020=92)(16030=93)(16040=94)(16050=95)(16060=96)(16070=97)(16080=98)(16090=99)(16100=100)(16110=101)

label var lmr2018 "Labor market region 2018"

* Company canton of respondent
rename lm06 company_canton_appr
rename em06 company_canton_empl

tab company_canton_empl if !missing(company_canton_appr) & company_canton_appr>0
tab company_canton_appr if !missing(company_canton_empl) & company_canton_empl>0 //appr and emp exclusive. Create unique variable.

gen company_canton=.
label var company_canton "Company canton of respondent (employees and apprenticeships)"
replace company_canton=company_canton_empl
replace company_canton=company_canton_appr if !missing(company_canton) & company_canton_appr>0 & !missing(company_canton_appr) | company_canton<0 & company_canton_appr>0 & !missing(company_canton_appr)

label define company_canton_lbl -9 "-9. Trifft nicht zu" -8 "-8. Keine Antwort" -7 "-7. Weiss nicht" -6 "-6. Arbeitgeber = anderer Privathaushalt"-4 "-4. Arbeitsstätte im Ausland" -1 "-1. Andere Erwerbstätige" 1 "Zürich" 2 "Bern" 3 "Luzern" 4 "Uri" 5 "Schwyz" 6 "Obwalden" 7 "Nidwalden" 8 "Glarus" 9 "Zug" 10 "Fribourg" 11 "Solothurn" 12 "Basel-Stadt" 13 "Basel-Landschaft" 14 "Schaffhausen"  15 "Appenzell A. Rh." 16 "Appenzell I. Rh." 17 "St. Gallen" 18 "Graubünden" 19 "Aargau" 20 "Thurgau" 21 "Ticino" 22 "Vaud" 23 "Valais" 24 "Neuchâtel" 25 "Genève" 26 "Jura"
label value company_canton company_canton_lbl

gen compc_title=company_canton
label var compc_title "Canton of respondent's company (abbr.)"
label define compc_title_lbl -9 "-9. Trifft nicht zu" -8 "-8. Keine Antwort" -7 "-7. Weiss nicht" -6 "-6. Arbeitgeber = anderer Privathaushalt"-4 "-4. Arbeitsstätte im Ausland" -1 "-1. Andere Erwerbstätige" 1 "ZH" 2 "BE" 3 "LU" 4 "UR" 5 "SZ" 6 "OW" 7 "NW" 8 "GL" 9 "ZG" 10 "FR" 11 "SO" 12 "BS" 13 "BL" 14 "SH"  15 "AR" 16 "AI" 17 "SG" 18 "GR" 19 "AG" 20 "TG" 21 "TI" 22 "VD" 23 "VS" 24 "NE" 25 "GE" 26 "JU"
label value compc_title compc_title_lbl

gen compc_title_complete=.
replace compc_title_complete=compc_title if compc_title>0
label define compc_title_c_lbl 1 "ZH" 2 "BE" 3 "LU" 4 "UR" 5 "SZ" 6 "OW" 7 "NW" 8 "GL" 9 "ZG" 10 "FR" 11 "SO" 12 "BS" 13 "BL" 14 "SH"  15 "AR" 16 "AI" 17 "SG" 18 "GR" 19 "AG" 20 "TG" 21 "TI" 22 "VD" 23 "VS" 24 "NE" 25 "GE" 26 "JU"
label value compc_title_complete compc_title_c_lbl
decode compc_title_complete, gen(compc_title_str)

* Gender
rename is01 male
recode male (2=0)
label define male_lbl 1 "Men" 0 "Women"
label value male male_lbl

* Civil status
gen married=0
replace married=1 if is03==2 | is03==6
label define married_lbl 1 "Married" 0 "Unmarried"
label value married married_lbl

* Gender x civil status
gen gend_civil=.
replace gend_civil=1 if male==0 & married==0
replace gend_civil=2 if male==0 & married==1
replace gend_civil=3 if male==1 & married==0
replace gend_civil=4 if male==1 & married==1
label define gend_civil_lbl 1 " Women, unmarried" 2 "Women, married" 3 "Men, unmarried" 4 "Men, married"
label value gend_civil gend_civil_lbl

* Children
recode famtyp (2=0)
label define famtyp_lbl 1 "With children below 15" 0 "Without children below 15"
label value famtyp famtyp_lbl

* Gender x children
gen gend_fam=.
replace gend_fam=1 if male==0 & famtyp==0
replace gend_fam=2 if male==0 & famtyp==1
replace gend_fam=3 if male==1 & famtyp==0
replace gend_fam=4 if male==1 & famtyp==1
label define gend_fam_lbl 1 " Women without children" 2 "Women with children" 3 "Men without children" 4 "Males with children"
label value gend_fam gend_famlbl

* Income class
rename bwu1k inc_class
label define inc_class_lbl 0 "No Income" 1 "Less than 13'000" 2"13'001 - 26'000" 3 "26'001 - 39'000" 4 "39'001 - 52'000" 5 "52'001 - 65'000" 6 "65'001 - 78'000" 7 "78'001 - 91'000" 8 "91'001 - 104'000" 9 "104'001 - 130'000" 10 "More than 130'000"
label value inc_class inc_class_lbl

* Urban
rename b028 urban
label def urban_lbl 1 "Urban areas" 2 "Periurban areas and rural centres" 3 "Rural areas"
label value urban urban_lbl

* Public sector
rename bmu8 public
replace public=. if public==-9
replace public=0 if public==1
replace public=1 if public==2
label def public_lbl 0 "Private Sector" 1 "Public Sector"
label value public public_lbl

* Age
rename bb03a age
gen agegroup=.
replace agegroup=1  if age>=15 & age<20
replace agegroup=2  if age>=20 & age<25
replace agegroup=3  if age>=25 & age<30
replace agegroup=4  if age>=30 & age<35
replace agegroup=5  if age>=35 & age<40
replace agegroup=6  if age>=40 & age<45
replace agegroup=7  if age>=45 & age<50
replace agegroup=8  if age>=50 & age<55
replace agegroup=9  if age>=55 & age<60
replace agegroup=10 if age>=60 & age<65
replace agegroup=11 if age>=65 & age<100

label define agegroup_lbl 1 "15-19" 2 "20-24" 3 "25-29" 4 "30-24" 5 "35-39" 6 "40-44" 7 "45-49" 8 "50-54" 9 "55-59" 10 "60-65" 11 "Over 65"
label value agegroup agegroup_lbl

* 2.6 Select sample and adjust weights
* Keep only workers
keep if bdu1>0 & bdu1<9 // excluded missings, apprentices, soldiers, unemployed, people in education, retirees
drop if teleworkable_isco==. | proximity_isco==.

* Create weights that are product of person weights and full-time equivalence factor
count if fulltime==0 & isco08>0
gen weights=ixpxhj*fulltime

* 2.7 Correct for essential sectors (see the Swiss Bundesrat's press release from March 16, 2020) 
gen essential_sector=0
replace essential_sector=1 if (noga2008_4d>=4631 & noga2008_4d<=4639) | (noga2008_4d>=4721 & noga2008_4d<=4729) // Grocery stores, take-aways, cantines, delivery services
replace essential_sector=1 if  noga2008_4d==4773 // Pharmacies
replace essential_sector=1 if noga2008_4d==4730 // Gas stations
replace essential_sector=1 if noga2008_4d==6419 // Banks
replace essential_sector=1 if (noga2008_4d>=5310 & noga2008_4d<=5320) // Post offices
replace essential_sector=1 if (noga2008_4d>=8411 & noga2008_4d<=8430) // Public sector & security
replace essential_sector=1 if  noga2008_4d==4520 | noga2008_4d==4540 // Auto garages
replace essential_sector=1 if (noga2008_4d>=8610 & noga2008_4d<=8899) // Health care sector

local essentialvars="proximity_isco teleworkable_isco"
foreach var in `essentialvars'{
	gen `var'_2=`var'
	label var  `var'_2 "`var' without essential correction"
	label var  `var' "`var' with essential correction"
}
replace proximity_isco=0 if essential_sector==1
replace teleworkable_isco=1 if essential_sector==1

* 2.8 Correct for public sector employment for comparison with short-time work
local publicvars="proximity_isco teleworkable_isco"
foreach var in `publicvars'{
	gen `var'_3=`var'
	label var  `var'_3 "`var' with essential and public sector correction"
}
replace proximity_isco_3=0 if public==1
replace teleworkable_isco_3=1 if public==1

* 2.9 Create mean indexes for categories of interest
local variables="NOGA_sector noga2008_seco noga2008_kza noga2008_gg company_canton male married gend_civil inc_class urban agegroup llmr2018 lmr2018 gend_fam famtyp"

foreach var in `variables'{
	bys `var': egen teleworkable_`var'=wtmean(teleworkable_isco) if !missing(teleworkable_isco), weight(weights)
	bys `var': egen proximity_`var'=wtmean(proximity_isco) if !missing(proximity_isco), weight(weights)
	bys `var': egen teleworkable_`var'_2=wtmean(teleworkable_isco_2) if !missing(teleworkable_isco_2), weight(weights)
	bys `var': egen proximity_`var'_2=wtmean(proximity_isco_2) if !missing(proximity_isco_2), weight(weights)
	bys `var': egen teleworkable_`var'_3=wtmean(teleworkable_isco_3) if !missing(teleworkable_isco_3), weight(weights)
	bys `var': egen proximity_`var'_3=wtmean(proximity_isco_3) if !missing(proximity_isco_3), weight(weights)
}

* 2.10 Save in privacy-conform format
gen obs_count=1

* Socio-economic information
local soceco_vars "agegroup gend_civil male inc_class urban noga2008_seco noga2008_kza"
foreach var in `soceco_vars'{
    preserve
	collapse (mean) proximity_`var' proximity_`var'_3  teleworkable_`var'_2 (sum) weights obs_count, by(`var')
	rename (proximity_`var' proximity_`var'_3  teleworkable_`var'_2) (lockdown_index lockdown_index_adj  home_office_index)
	save "B_03_privacyconform_`var'.dta", replace
	restore
}

* Geographic information
rename compc_title company_canton_title
merge m:1 lmr2018 using "B_01_input_geo_lmr2018_title", keep(match master)
drop _merge

local geo_vars "company_canton llmr2018 lmr2018"
foreach var in `geo_vars'{
    preserve
	collapse (mean) proximity_`var' proximity_`var'_3  teleworkable_`var'_2 (sum) weights obs_count, by(`var' `var'_title)
	rename (proximity_`var' proximity_`var'_3  teleworkable_`var'_2) (lockdown_index lockdown_index_adj  home_office_index)
	save "B_03_privacyconform_`var'.dta", replace
	restore
}
